package Dao;

import entity.Goods;
import entity.User;
import util.DatabaseUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsDaoImpl extends BasDao implements GoodsDao{
    Connection conn;
    public GoodsDaoImpl(Connection conn) {
        super(conn);
    }

    @Override
    public Goods getCommod() {
        ResultSet rs = null;
        Goods goods =null;
        PreparedStatement pstmt = null;
        String sql = "SELECT * FROM `goods` where id=9";
        try {
            rs =  super.executeQuery(sql);
            while (rs.next()){
                goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setCover(rs.getString("cover"));
                goods.setImage1(rs.getString("image1"));
                goods.setImage2(rs.getString("image2"));
                goods.setIntro(rs.getString("intro"));
                goods.setPrice(rs.getDouble("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setTypeId(rs.getInt("type_id"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, pstmt, rs);
        }
        return goods;
    }
//商品热销榜
    @Override
    public List<Goods> getCommodity(String name) {
        ResultSet rs = null;
        Goods goods =null;
        List<Goods> list = new ArrayList<>();
        PreparedStatement pstmt = null;
        String sql = "SELECT *,type.name FROM `goods` left join type on goods.type_id=type.id left join datime on datime.id=goods.id" +
                " where (goods.`name` like ?) order by goods.stock desc limit 0,6";
        try {
            rs =  super.executeQuery(sql,"%"+name+"%");
            while (rs.next()){
                goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setCover(rs.getString("cover"));
                goods.setImage1(rs.getString("image1"));
                goods.setImage2(rs.getString("image2"));
                goods.setIntro(rs.getString("intro"));
                goods.setPrice(rs.getDouble("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setTypeId(rs.getInt("type_id"));
                goods.setTypeName(rs.getString("type.name"));
                list.add(goods);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, pstmt, rs);
        }
        return list;
    }
    //商品新品榜
    @Override
    public List<Goods> getProduct(String name) {
        ResultSet rs = null;
        Goods goods =null;
        List<Goods> list = new ArrayList<>();
        PreparedStatement pstmt = null;
        String sql = "SELECT *,type.name FROM `goods` left join type on goods.type_id=type.id left join datime on datime.id=goods.id " +
                "where (goods.`name` like ?) limit 5,6";
        try {
            rs =  super.executeQuery(sql,"%"+name+"%");
            while (rs.next()){
                goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setCover(rs.getString("cover"));
                goods.setImage1(rs.getString("image1"));
                goods.setImage2(rs.getString("image2"));
                goods.setIntro(rs.getString("intro"));
                goods.setPrice(rs.getDouble("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setTypeId(rs.getInt("type_id"));
                goods.setTypeName(rs.getString("type.name"));
                list.add(goods);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, pstmt, rs);
        }
        return list;
    }

    @Override
    public Goods getParticulars(int id) {
        ResultSet rs = null;
        Goods goods =null;
        PreparedStatement pstmt = null;
        String sql = "SELECT *,type.name FROM `goods` left join type on goods.type_id=type.id  where goods.id=?";
        try {
            rs =  super.executeQuery(sql,id);
            while (rs.next()){
                goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setCover(rs.getString("cover"));
                goods.setImage1(rs.getString("image1"));
                goods.setImage2(rs.getString("image2"));
                goods.setIntro(rs.getString("intro"));
                goods.setPrice(rs.getDouble("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setTypeId(rs.getInt("type_id"));
                goods.setTypeName(rs.getString("type.name"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, pstmt, rs);
        }
        return goods;
    }

    @Override
    public List<Goods> getSeries(int id) {
        ResultSet rs = null;
        Goods goods =null;
        List<Goods> list = new ArrayList<>();
        PreparedStatement pstmt = null;
        String fuzzyQuery = "";
        if (id!=0){
            fuzzyQuery=" and type.id=?";
        }
        String sql = "select * from goods left join type on type.id=goods.type_id where 1=1 "+fuzzyQuery;
        try {
            if (id!=0) {
                rs = super.executeQuery(sql, id);
            }else {
                rs = super.executeQuery(sql);
            }
            while (rs.next()){
                goods = new Goods();
                goods.setId(rs.getInt("id"));
                goods.setName(rs.getString("name"));
                goods.setCover(rs.getString("cover"));
                goods.setImage1(rs.getString("image1"));
                goods.setImage2(rs.getString("image2"));
                goods.setIntro(rs.getString("intro"));
                goods.setPrice(rs.getDouble("price"));
                goods.setStock(rs.getInt("stock"));
                goods.setTypeId(rs.getInt("type_id"));
                goods.setTypeName(rs.getString("type.name"));
                list.add(goods);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            DatabaseUtil.closeAll(null, pstmt, rs);
        }
        return list;
    }

    public static void main(String[] args) {
        DatabaseUtil baseDao=new DatabaseUtil();
        Connection conn = baseDao.getConnection();
        GoodsDao goodsDao = new GoodsDaoImpl(conn);
        //List<Goods> num = goodsDao.getCommodity("");
        List<Goods> go = goodsDao.getSeries(0);
        System.out.println(go);
    }
}
